package com.FOD.util;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import com.FOD.entities.EmpleadoHorario;
import com.FOD.entities.PacienteUsuario;
import com.FOD.entities.User;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class SessionDBHandler extends SQLiteOpenHelper {

	private static final int DATABASE_VERSION = 1;

	private static final String DATABASE_NAME = "user_session_DB";

	private static final String USERS_TABLE = "usuarios";
	private static final String SCHEDULE_EMPLOYE = "empleado_horario";
	private static final String PATIENT_USER = "paciente_usuario";

	// columnas de tabla usuarios
	private static final String KEY_ID = "id";
	private static final String KEY_NICKNAME = "nickname";
	private static final String KEY_PASSWORD = "password";
	private static final String KEY_NAME = "name";
	private static final String KEY_MAIL = "mail";
	private static final String KEY_USER_TYPE = "user_type";
	private static final String KEY_AGE = "age";
	private static final String KEY_SEX = "sex";
	private static final String KEY_DEPENDENCY = "dependency";
	private static final String KEY_SPORT = "sport";
	private static final String KEY_PHONE = "phone";
	private static final String KEY_CREATE_DATE = "create_date";

	// columnas de empleado_horario
	// private static final String KEY_ID = "id";
	private static final String KEY_USER_ID = "usuario_id";
	private static final String KEY_START = "inicio";
	private static final String KEY_FINISH = "final";

	// Columnas de paciente_usuario
	// private static final String KEY_ID = "id";
	// private static final String KEY_USER_ID = "usuario_id";
	private static final String KEY_OCUPATION = "ocupacion";
	private static final String KEY_MARTIAL_STARTUS = "estado_civil";
	private static final String KEY_SMOKING = "tabaquismo";
	private static final String KEY_SCHOOL_GRADE = "escolaridad";
	private static final String KEY_BLOOD_TYPE = "dominancia";
	private static final String KEY_ALCOHOLISM = "alcoholismo";

	public SessionDBHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Crear tablas
	@Override
	public void onCreate(SQLiteDatabase db) {
		String CREATE_USERS_TABLE = "CREATE TABLE " + USERS_TABLE + "("
				+ KEY_ID + " INTEGER PRIMARY KEY," + KEY_NICKNAME + " TEXT,"
				+ KEY_PASSWORD + " TEXT," + KEY_NAME + " TEXT," + KEY_MAIL
				+ " TEXT," + KEY_USER_TYPE + " INTEGER," + KEY_AGE
				+ " INTEGER," + KEY_SEX + " INTEGER," + KEY_DEPENDENCY
				+ " TEXT," + KEY_SPORT + " TEXT," + KEY_PHONE + " INTEGER,"
				+ KEY_CREATE_DATE + " DATE" + ")";

		String CREATE_SCHEDULE_TABLE = "CREATE TABLE " + SCHEDULE_EMPLOYE + "("
				+ KEY_ID + " INTEGER PRIMARY KEY," + KEY_USER_ID + " INTEGER,"
				+ KEY_START + " TEXT," + KEY_FINISH + " TEXT" + ")";

		String CREATE_PATIENT_USER_TABLE = "CREATE TABLE " + PATIENT_USER + "("
				+ KEY_ID + " INTEGER PRIMARY KEY," + KEY_USER_ID + " INTEGER,"
				+ KEY_OCUPATION + " TEXT," + KEY_MARTIAL_STARTUS + " INTEGER,"
				+ KEY_SMOKING + " BOOLEAN," + KEY_SCHOOL_GRADE + " INTEGER,"
				+ KEY_BLOOD_TYPE + " TEXT," + KEY_ALCOHOLISM + " BOOLEAN" + ")";

		db.execSQL(CREATE_USERS_TABLE);
		db.execSQL(CREATE_SCHEDULE_TABLE);
		db.execSQL(CREATE_PATIENT_USER_TABLE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + USERS_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + SCHEDULE_EMPLOYE);
		db.execSQL("DROP TABLE IF EXISTS " + PATIENT_USER);

		// Create tables again
		onCreate(db);
	}

	public void addUser(User user) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_ID, user.getId());
		values.put(KEY_NICKNAME, user.getNickname());
		values.put(KEY_PASSWORD, user.getPassword());
		values.put(KEY_NAME, user.getName());
		values.put(KEY_MAIL, user.getMail());
		values.put(KEY_USER_TYPE, user.getType());
		values.put(KEY_AGE, user.getAge());
		values.put(KEY_SEX, user.getSex());
		values.put(KEY_DEPENDENCY, user.getDependecy());
		values.put(KEY_SPORT, user.getSport());
		values.put(KEY_PHONE, user.getPhone());
		values.put(KEY_CREATE_DATE, user.getCreateDate().toString());

		db.insert(USERS_TABLE, null, values);
		db.close();
	}

	public void addScheduleEmploye(EmpleadoHorario eh) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_ID, eh.getId());
		values.put(KEY_USER_ID, eh.getUsuario_id());
		values.put(KEY_START, eh.getInicio());
		values.put(KEY_FINISH, eh.getH_final());

		db.insert(SCHEDULE_EMPLOYE, null, values);
		db.close();
	}

	public void addPatientUser(PacienteUsuario pu) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_ID, pu.getId());
		values.put(KEY_USER_ID, pu.getUsuario_id());
		values.put(KEY_OCUPATION, pu.getOcupacion());
		values.put(KEY_MARTIAL_STARTUS, pu.getEstadoCivil());
		values.put(KEY_SMOKING, pu.isTabaquismo());
		values.put(KEY_SCHOOL_GRADE, pu.getEscolaridad());
		values.put(KEY_BLOOD_TYPE, pu.getDominancia());
		values.put(KEY_ALCOHOLISM, pu.isAlcoholismo());

		db.insert(PATIENT_USER, null, values);
		db.close();
	}

	public User getUserDetails() {
		User user = new User();
		String selectQuery = "SELECT  * FROM " + USERS_TABLE;

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		// Move to first row
		cursor.moveToFirst();
		if (cursor.getCount() > 0) {
			user.setId(cursor.getInt(0));
			user.setNickname(cursor.getString(1));
			user.setPassword(cursor.getString(2));
			user.setName(cursor.getString(3));
			user.setMail(cursor.getString(4));
			user.setType(cursor.getInt(5));
			user.setAge(cursor.getInt(6));
			user.setSex(cursor.getInt(7));
			user.setDependecy(cursor.getString(8));
			user.setSport(cursor.getString(9));
			user.setPhone(cursor.getInt(10));

			// d1 = dateFormatOfStringInDB.parse(cursor.getString(11));
			user.setCreateDate(new Date(cursor.getString(11)));

		}
		cursor.close();
		db.close();
		return user;
	}

	public EmpleadoHorario getScheduleEmployeDetails() {
		EmpleadoHorario scheduleEmploye = new EmpleadoHorario();
		String selectQuery = "SELECT  * FROM " + SCHEDULE_EMPLOYE;

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		// Move to first row
		cursor.moveToFirst();
		if (cursor.getCount() > 0) {
			scheduleEmploye.setId(cursor.getInt(0));
			scheduleEmploye.setUsuario_id(cursor.getInt(1));
			scheduleEmploye.setInicio(cursor.getString(2));
			scheduleEmploye.setH_final(cursor.getString(3));
		}
		cursor.close();
		db.close();
		return scheduleEmploye;
	}

	public PacienteUsuario getPatientUserDetails() {
		PacienteUsuario pu = new PacienteUsuario();
		String selectQuery = "SELECT  * FROM " + PATIENT_USER;

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		// Move to first row
		cursor.moveToFirst();
		if (cursor.getCount() > 0) {
			pu.setId(cursor.getInt(0));
			pu.setUsuario_id(cursor.getInt(1));
			pu.setOcupacion(cursor.getString(2));
			pu.setEstadoCivil(cursor.getInt(3));
			pu.setTabaquismo(cursor.getInt(4) == 0 ? false : true);
			pu.setEscolaridad(cursor.getInt(5));
			pu.setDominancia(cursor.getString(6));
			pu.setAlcoholismo(cursor.getInt(7) == 0 ? false : true);
		}
		cursor.close();
		db.close();
		return pu;
	}
	
	public int getUserType(){
		String selectQuery = "SELECT "+KEY_USER_TYPE+" FROM " + USERS_TABLE;

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		// Move to first row
		cursor.moveToFirst();
		
		return cursor.getInt(0);
	}

	public int getRowCount() {
		String countQuery = "SELECT * FROM " + USERS_TABLE;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		int rowCount = cursor.getCount();
		db.close();
		return rowCount;
	}

	public void resetTables() {
		SQLiteDatabase db = this.getWritableDatabase();
		// Borrar todas las tablas
		db.delete(USERS_TABLE, null, null);
		db.delete(SCHEDULE_EMPLOYE, null, null);
		db.delete(PATIENT_USER, null, null);
		db.close();
	}

}
